﻿/* ----------------------------------------------------------------------------   
 * Copyright:
 * Wuxi Huaxia Computer Technology Co.,Ltd. All rights reserved. 
 *
 * Module Description:
 * DbHelper,数据库连接，执行程序块
 *  
 * Namespace:
 *  Wxhx.EntLib.Data
 *
 *  * Class Description:
 *
 *
 * Comment 					        Revision	    Date	  Coder
 * -----------------------------    --------    --------  --------------
 * Created							1.0		    2006-5-1   huyb@wxhx.com
 * Updated							1.0		
 *
 *
 * ------------------------------------------------------------------------------*/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using SaleStat.COM.SQLHelper;

namespace SES.COM.SQLHelper
{
    public class DbHelper
    {
        #region DataSet
        #region 取回DataSet格式数据集,有参数的方式
        /// <summary>
        /// 取回DataSet格式数据集,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return null;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                DataSet ds = db.ExecuteDataSet(dbCommand);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 取回DataSet格式数据集,无参数方式
        /// <summary>
        /// 取回DataSet格式数据集,无参数方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                DataSet ds = db.ExecuteDataSet(dbCommand);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行已组合好的SQL文,返回DataSet格式数据集
        /// <summary>
        /// 执行已组合好的SQL文,返回DataSet格式数据集
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                DataSet ds = db.ExecuteDataSet(dbCommand);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region ExecuteNonQuery
        #region 执行非查询的数据库操作,有参数的方式
        /// <summary>
        /// 执行非查询的数据库操作,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return -1;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return -1;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                int rtn = db.ExecuteNonQuery(dbCommand);

                return rtn;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行非查询的数据库操作,无参数的方式
        /// <summary>
        /// 执行非查询的数据库操作,无参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return -1;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                int rtn = db.ExecuteNonQuery(dbCommand);

                return rtn;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行组合好的非查询的SQL文
        /// <summary>
        /// 执行组合好的非查询的SQL文
        /// </summary>
        /// <param name="sqlText">组合好的SQL文</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                int rtn = db.ExecuteNonQuery(dbCommand);

                return rtn;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region ExecuteScalar
        #region 取回object格式数据集,有参数的方式
        /// <summary>
        /// 取回object格式数据集,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");


            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return null;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                object obj = db.ExecuteScalar(dbCommand);

                return obj;
            }
            catch (Exception ex)
            {

                throw (ex);
            }
        }
        #endregion

        #region 取回object格式数据集,无参数的方式
        /// <summary>
        /// 取回object格式数据集,无参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();
            string sqlString;

            try
            {
                sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(XmlProcedure.BuildSqlString(pageId, sqlId));

                object obj = db.ExecuteScalar(dbCommand);

                return obj;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行组合好的SQL文,取回object格式数据集
        /// <summary>
        /// 执行组合好的SQL
        /// </summary>
        /// <param name="sqlText">组合好的SQL</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                object obj = db.ExecuteScalar(dbCommand);

                return obj;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 取回object格式数据集,有参数的方式
        /// <summary>
        /// 取回object格式数据集,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>object</returns>
        public static object ExecuteCombineScalar(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildCombineString(pageId, sqlId, parmsList);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return null;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                object obj = db.ExecuteScalar(dbCommand);

                return obj;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region ExecuteReader
        #region 取回DataReader格式数据集,有参数的方式
        /// <summary>
        /// 取回DataReader格式数据集,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>IDataReader</returns>
        public static IDataReader ExecuteReader(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                    return null;

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                IDataReader idr = db.ExecuteReader(dbCommand);

                return idr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 取回DataReader格式数据集,无参数的方式
        /// <summary>
        /// 取回DataReader格式数据集,无参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <returns>IDataReader</returns>
        public static IDataReader ExecuteReader(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                IDataReader idr = db.ExecuteReader(dbCommand);

                return idr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行有客户组合好的SQL,取回DataReader格式数据集
        /// <summary>
        /// 执行有客户组合好的SQL,取回DataReader格式数据集
        /// </summary>
        /// <param name="sqlText">客户组合的SQL</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                IDataReader idr = db.ExecuteReader(dbCommand);

                return idr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region DataTable
        #region 取得DataTable格式数据表,有参数的方式
        /// <summary>
        /// 取得DataTable格式数据表,有参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <param name="parmsList">SQLID 所指定的SQL文的参数列表</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);
                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                    return null;

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                DataTable dt = new DataTable();
                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 取得DataTable格式数据表,无参数的方式
        /// <summary>
        /// 取得DataTable格式数据表,无参数的方式
        /// </summary>
        /// <param name="sqlId">SQL标识,存储于XML文件中,唯一指定一条SQL文</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                DataTable dt = new DataTable();
                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 执行组合好的SQL,返回DataTable各式的数据表
        /// <summary>
        /// 执行组合好的SQL,返回DataTable各式的数据表
        /// </summary>
        /// <param name="sqlText">组合好的SQ</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                DataTable dt = new DataTable();
                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 取得DataTable格式数据表,带参数方式
        /// <summary>
        /// 取得DataTable格式数据表，带参数方式
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="parmsList"></param>
        /// <returns></returns>
        public static DataTable ExecuteCombine(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");


            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlText = XmlProcedure.BuildCombineString(pageId, sqlId, parmsList);

                if (sqlText == null || sqlText.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);
                DataTable dt = new DataTable();
                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region 事务处理
        #region 批量插入、更新、删除数据库记录
        /// <summary>
        /// 批量插入、更新、删除数据库记录
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="parmsLists"></param>
        /// <returns></returns>
        public static bool ExecuteBulkQuery(string pageId, string sqlId, ArrayList parmsLists)
        {
            Database db = DatabaseFactory.CreateDatabase();
            bool rtn = false;

            using (DbConnection con = db.CreateConnection())
            {
                con.Open();
                DbTransaction trans = con.BeginTransaction();
                try
                {
                    int count = 0;

                    string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                    if (sqlString == null || sqlString.Length == 0)
                        return false;

                    // 初始化Sql命令
                    DbCommand dbCommand = null;

                    count = parmsLists.Count;

                    // 加入参数信息
                    ArrayList parms = new ArrayList();
                    for (int i = 0; i < count; i++)
                    {
                        // 清空
                        parms.Clear();

                        // 取得参数信息
                        parms = (ArrayList)parmsLists[i];

                        // 参数对象数组
                        ArrayList xmlParmsList = new ArrayList();

                        // 生成参数
                        if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parms, ref xmlParmsList))
                        {
                            return false;
                        }

                        // 加入参数到Database。
                        dbCommand = null;
                        dbCommand = db.GetSqlStringCommand(sqlString);
                        foreach (XmlParameter xmlPara in xmlParmsList)
                        {
                            db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                        }

                        if (dbCommand != null)
                            db.ExecuteNonQuery(dbCommand, trans);
                    }

                    trans.Commit();
                    rtn = true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    rtn = false;

                    throw ex;
                }

                con.Close();
            }

            return rtn;
        }
        #endregion

        #region 对多条数据库操作命令(主表和子表同时操作)，执行事务处理
        /// <summary>
        /// 对多条数据库操作命令(主表和子表同时操作)，执行事务处理
        /// </summary>
        /// <param name="cmdParentList"></param>
        /// <param name="cmdChildList"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(ArrayList cmdParentList, ArrayList cmdChildList)
        {
            Database db = DatabaseFactory.CreateDatabase();

            using (DbConnection con = db.CreateConnection())
            {
                // 打开连接
                con.Open();

                // 开始事务处理
                DbTransaction trans = con.BeginTransaction();

                try
                {
                    // 数据库操作结果
                    int result = 0;

                    // 数据库操作命令对象
                    DbCommand cmd;

                    // 针对子表的操作命令的数目
                    int count = 0;
                    count = cmdChildList.Count;

                    // 循环执行所有针对子表的操作命令
                    for (int i = 0; i < count; i++)
                    {
                        // 获取更新子表的操作命令
                        cmd = (DbCommand)cmdChildList[i];

                        if (cmd != null)
                        {
                            result = db.ExecuteNonQuery(cmd, trans);
                        }
                    }

                    // 针对主表的操作命令的数目
                    count = cmdParentList.Count;

                    // 循环执行所有针对主表的操作命令
                    for (int j = 0; j < count; j++)
                    {
                        // 获取更新主表的操作命令
                        cmd = (DbCommand)cmdParentList[j];

                        if (cmd != null)
                        {
                            result = db.ExecuteNonQuery(cmd, trans);

                            // 操作失败时
                            if (result <= 0)
                            {
                                // 回滚事务处理
                                trans.Rollback();

                                // 操作失败
                                return false;
                            }
                        }
                    }

                    // 提交事务处理
                    trans.Commit();

                    // 操作成功
                    return true;
                }
                catch (Exception ex)
                {
                    // 回滚事务处理
                    trans.Rollback();
                    throw ex;
                }
                finally
                {
                    // 清空事务
                    trans.Dispose();

                    // 关闭连接
                    con.Close();
                }
            }
        }
        #endregion

        #region 对多条数据库操作命令，执行事务处理
        /// <summary>
        /// 对多条数据库操作命令，执行事务处理
        /// </summary>
        /// <param name="cmdList"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(ArrayList cmdList)
        {
            Database db = DatabaseFactory.CreateDatabase();

            using (DbConnection con = db.CreateConnection())
            {
                // 打开连接
                con.Open();

                // 开始事务处理
                DbTransaction trans = con.BeginTransaction();

                try
                {
                    // 数据库操作命令数目
                    int count = 0;
                    count = cmdList.Count;

                    // 数据库操作结果
                    int result = 0;

                    // 数据库操作命令对象
                    DbCommand cmd;

                    // 循环执行所有数据库操作命令
                    for (int i = 0; i < count; i++)
                    {
                        cmd = (DbCommand)cmdList[i];

                        if (cmd != null)
                        {
                            result = db.ExecuteNonQuery(cmd, trans);
                        }
                    }

                    // 提交事务处理
                    trans.Commit();

                    // 操作成功
                    return true;
                }
                catch (Exception ex)
                {
                    // 回滚事务处理
                    trans.Rollback();
                    throw ex;
                }
                finally
                {
                    // 清空事务
                    trans.Dispose();

                    // 关闭连接
                    con.Close();
                }
            }
        }
        #endregion

        #region 进行事务处理，有参数的情况,SQL语句固定
        /// <summary>
        /// 对单表进行事务处理，有参数的情况
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="parmsList"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();
            string sqlString;
            bool rtn = false;

            sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

            if (sqlString == null || sqlString.Length == 0)
                return false;

            DbCommand dbCommand = db.GetSqlStringCommand(sqlString);
            ArrayList xmlParmsList = new ArrayList();

            if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
            {
                return false;
            }

            foreach (XmlParameter xmlPara in xmlParmsList)
            {
                db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
            }

            using (DbConnection con = db.CreateConnection())
            {
                con.Open();
                DbTransaction trans = con.BeginTransaction();
                try
                {
                    db.ExecuteNonQuery(dbCommand, trans);

                    trans.Commit();
                    rtn = true;
                }
                catch
                {
                    trans.Rollback();
                    rtn = false;
                }

                con.Close();

                return rtn;
            }
        }
        #endregion

        #region 进行事务处理，无参数的情况,SQL语句固定
        /// <summary>
        /// 对单表进行事务处理，无参数的情况
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();
            string sqlString;
            bool rtn = false;

            sqlString = SaleStat.COM.SQLHelper.XmlProcedure.BuildSqlString(pageId, sqlId);

            if (sqlString == null || sqlString.Length == 0)
                return false;

            DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

            using (DbConnection con = db.CreateConnection())
            {
                con.Open();
                DbTransaction trans = con.BeginTransaction();
                try
                {
                    db.ExecuteNonQuery(dbCommand, trans);

                    trans.Commit();
                    rtn = true;
                }
                catch
                {
                    trans.Rollback();
                    rtn = false;
                }

                con.Close();

                return rtn;
            }
        }
        #endregion

        #region 进行事务处理，执行用户组合好的SQL,SQL语句固定
        /// <summary>
        /// 对单表进行事务处理，执行用户组合好的SQL
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(string sqlText)
        {
            if (sqlText == null || sqlText.Length == 0) throw new ArgumentNullException("sqlText");

            bool rtn = false;

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

            using (DbConnection con = db.CreateConnection())
            {
                con.Open();
                DbTransaction trans = con.BeginTransaction();
                try
                {
                    db.ExecuteNonQuery(dbCommand, trans);

                    trans.Commit();
                    rtn = true;
                }
                catch
                {
                    trans.Rollback();
                    rtn = false;
                }

                con.Close();

                return rtn;
            }
        }
        #endregion
        #endregion

        #region GetDbCommand
        #region 将带参数的SQL语句封装成DbCommand对象
        /// <summary>
        /// 将带参数的SQL语句封装成DbCommand对象
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="parmsList"></param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            Database db = DatabaseFactory.CreateDatabase();
            string sqlString;



            //函数开始Log


            try
            {
                sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);
                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return null;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }

                //函数结束Log


                return dbCommand;
            }
            catch (Exception ex)
            {

                throw (ex);
            }

        }
        #endregion

        #region 将无参数的SQL语句封装成DbCommand对象
        /// <summary>
        /// 将无参数的SQL语句封装成DbCommand对象
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");

            Database db = DatabaseFactory.CreateDatabase();
            string sqlString;



            //函数开始Log


            try
            {
                sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                //函数结束Log


                return dbCommand;
            }
            catch (Exception ex)
            {

                throw (ex);
            }

        }
        #endregion

        #region 将带参数的SQL语句封装成DbCommand对象,传DB
        /// <summary>
        /// 将带参数的SQL语句封装成DbCommand对象,传DB
        /// </summary>
        /// <param name="db"></param>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="parmsList"></param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(Database db, string pageId, string sqlId, ArrayList parmsList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");

            string sqlString;
            //函数开始Log


            try
            {
                sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);
                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

                if (sqlString == null || sqlString.Length == 0)
                    return null;


                ArrayList xmlParmsList = new ArrayList();

                if (!XmlProcedure.BuildSqlParms(pageId, sqlId, parmsList, ref xmlParmsList))
                {
                    return null;
                }

                foreach (XmlParameter xmlPara in xmlParmsList)
                {
                    db.AddInParameter(dbCommand, xmlPara.ParamName, xmlPara.ParamType, xmlPara.ParamValue);
                }


                //函数结束Log


                return dbCommand;
            }
            catch (Exception ex)
            {

                throw (ex);
            }

        }
        #endregion

        #region 将无参数的SQL语句封装成DbCommand对象,传DB
        /// <summary>
        /// 将无参数的SQL语句封装成DbCommand对象,传DB
        /// </summary>
        /// <param name="db"></param>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(Database db, string pageId, string sqlId)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");


            //函数开始Log

            try
            {
                string sqlString = XmlProcedure.BuildSqlString(pageId, sqlId);

                if (sqlString == null || sqlString.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlString);


                //函数结束Log

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 将带参数的SQL语句封装成DbCommand对象,传DB
        // 综合查询
        // 如果select 为空,则置为null或空值
        /// <summary>
        /// 将带参数的SQL语句封装成DbCommand对象,传DB
        /// </summary>
        /// <param name="db"></param>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="select"></param>
        /// <param name="parmsList"></param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(Database db, string pageId, string sqlId, string select, ArrayList parmsList)
        {
            if (db == null) throw new ArgumentNullException("db");
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (parmsList == null || parmsList.Count == 0) throw new ArgumentNullException("parmsList");



            try
            {
                string sqlText = XmlProcedure.BuildCombineString(pageId, sqlId, parmsList);

                if (sqlText == null || sqlText.Length == 0)
                    return null;

                if (!string.IsNullOrEmpty(select))
                    sqlText = "Select " + select + sqlText;

                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);

                //函数结束Log

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
        #endregion

        #region DBNULL转换
        #region 传参：行和列名
        /// <summary>
        /// DBNULL转换
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colname"></param>
        /// <returns></returns>
        public static object Nc(DataRow row, String colName)
        {
            object rtn = null;

            try
            {
                string coltype;
                coltype = row.Table.Columns[colName].DataType.ToString();

                if (Convert.IsDBNull(row[colName]))
                {
                    switch (coltype)
                    {
                        case "System.Boolean":		// bit系
                            rtn = false;
                            break;

                        case "System.Int16":		// 数値系
                            rtn = 0;
                            break;
                        case "System.Int32":
                            rtn = 0;
                            break;
                        case "System.Int64":
                            rtn = 0;
                            break;
                        case "System.Decimal":
                            rtn = (Decimal)0;
                            break;
                        case "System.Double":
                            rtn = (Double)0;
                            break;
                        case "System.Single":
                            rtn = (Single)0;
                            break;

                        case "System.String":		// 文字列系
                            rtn = String.Empty;
                            break;
                        default:					// その他
                            // その他はそのままの値を返す
                            rtn = row[colName];
                            break;
                    }
                }
                else
                {
                    switch (coltype)
                    {
                        case "System.String":		// 文字列系
                            rtn = row[colName].ToString().Trim();
                            break;
                        default:					// その他
                            rtn = row[colName];
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return rtn;
        }
        #endregion

        #region 传参：行，列索引和为空时返回的字符串
        /// <summary>
        /// DBNULL转换
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colname"></param>
        /// <returns></returns>
        public static object Nc(DataRow row, int colIndex, string strDef)
        {
            object rtn = null;

            try
            {
                string coltype;
                coltype = row.Table.Columns[colIndex].DataType.ToString();

                if (Convert.IsDBNull(row[colIndex]))
                {
                    return strDef;
                }
                else
                {
                    switch (coltype)
                    {
                        case "System.String":		// 文字列系
                            rtn = row[colIndex].ToString().Trim();
                            break;
                        default:					// その他
                            rtn = row[colIndex];
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return rtn;
        }
        #endregion

        #region 传参：行和列索引
        /// <summary>
        /// DBNULL转换
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colname"></param>
        /// <returns></returns>
        public static object Nc(DataRow row, int colIndex)
        {
            object rtn = null;

            try
            {
                string coltype;
                coltype = row.Table.Columns[colIndex].DataType.ToString();

                if (Convert.IsDBNull(row[colIndex]))
                {
                    switch (coltype)
                    {
                        case "System.Boolean":		// 
                            rtn = false;
                            break;

                        case "System.Int16":		// 
                            rtn = 0;
                            break;
                        case "System.Int32":
                            rtn = 0;
                            break;
                        case "System.Int64":
                            rtn = 0;
                            break;
                        case "System.Decimal":
                            rtn = (Decimal)0;
                            break;
                        case "System.Double":
                            rtn = (Double)0;
                            break;
                        case "System.Single":
                            rtn = (Single)0;
                            break;

                        case "System.String":		// 
                            rtn = String.Empty;
                            break;
                        default:					// 

                            rtn = row[colIndex];
                            break;
                    }
                }
                else
                {
                    switch (coltype)
                    {
                        case "System.String":		// 文字列系
                            rtn = row[colIndex].ToString().Trim();
                            break;
                        default:					// その他
                            rtn = row[colIndex];
                            break;
                    }
                }
            }
            catch (Exception ex)
            {

                throw (ex);
            }

            return rtn;
        }
        #endregion

        #region 传参：对象
        /// <summary>
        /// 数据库NULL值转换
        /// </summary>
        /// <param name="obj">要转换的值</param>
        /// <returns>系统要求的值</returns>
        public static object Nc(object obj)
        {
            object returnObject = null;

            try
            {
                string coltype;
                coltype = obj.GetType().ToString();

                if (Convert.IsDBNull(obj))
                {
                    switch (coltype)
                    {
                        case "System.Boolean":		// 
                            returnObject = false;
                            break;
                        case "System.Int16":		// 
                            returnObject = 0;
                            break;
                        case "System.Int32":
                            returnObject = 0;
                            break;
                        case "System.Int64":
                            returnObject = 0;
                            break;
                        case "System.Decimal":
                            returnObject = (Decimal)0;
                            break;
                        case "System.Double":
                            returnObject = (Double)0;
                            break;
                        case "System.Single":
                            returnObject = (Single)0;
                            break;
                        case "System.String":
                            returnObject = String.Empty;
                            break;
                        case "System.DateTime":
                            returnObject = String.Empty;
                            break;
                        default:
                            returnObject = obj;
                            break;
                    }
                }
                else
                {
                    switch (coltype)
                    {
                        case "System.String":		// 文字列系
                            returnObject = obj.ToString().Trim();
                            break;
                        case "System.DateTime":
                            returnObject = Convert.ToDateTime(obj).ToString("yyyy/MM/dd");
                            break;
                        default:					// その他
                            returnObject = obj;
                            break;
                    }
                }
            }
            catch (Exception ex)
            {

                throw (ex);
            }

            return returnObject;
        }
        #endregion
        #endregion

        #region Union 暂时不使用
        /// <summary>
        /// 有Union 时
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="select"></param>
        /// <param name="parmList"></param>
        /// <returns></returns>
        public static DataTable ExecuteCombine(string pageId, string sqlId, string select, string[] Changeselect, string[] Change, ArrayList paramList)
        {
            if (pageId == null || pageId.Length == 0) throw new ArgumentNullException("pageId");
            if (sqlId == null || sqlId.Length == 0) throw new ArgumentNullException("sqlId");
            if (select == null || select.Length == 0) throw new ArgumentNullException("select");
            if (paramList == null || paramList.Count == 0) throw new ArgumentNullException("parmsList");
            int n = Changeselect.Length;
            int m = Change.Length;
            string selectnew = select;
            if (m > 0)
            {
                for (int k = 0; k < m; k++)
                {
                    if (select.Contains(Change[k].ToString()))
                    {
                        selectnew = selectnew.Replace(Change[k], Changeselect[k]);
                    }
                    else
                    {
                        return null;
                    }
                }
                selectnew = " UNION ALL  Select " + selectnew;
            }
            else
            {
                return null;
            }

            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                string sqlText = XmlProcedure.BuildCombineString(pageId, sqlId, paramList);

                if (sqlText == null || sqlText.Length == 0)
                    return null;
                if (sqlText.Contains("UNION"))
                {
                    sqlText = sqlText.Replace("UNION", selectnew);
                }
                sqlText = "Select " + select + sqlText;
                DbCommand dbCommand = db.GetSqlStringCommand(sqlText);
                DataTable dt = new DataTable();
                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region union组合sql语句
        /// <summary>
        /// 组合sql语句
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="keyParam"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static DataTable ExecuteSqlCombine(string pageId, string sqlId, string[] keyParam, ArrayList paramList)
        {
            //sql语句集合
            ArrayList arrSqlList = new ArrayList();

            Database db = DatabaseFactory.CreateDatabase();

            //初始sql语句
            string sqlText = "";

            //转换前的初始sql
            string sql = "";

            try
            {

                //依次把keyParam数组中元素加到参数集合列表，得到sql语句
                for (int i = 0; i < keyParam.Length; i++)
                {
                    paramList.Add(keyParam[i]);

                    //得到sql语句
                    sqlText = XmlProcedure.BuildCombineString(pageId, sqlId, paramList);

                    //去掉参数集合中新加的参数
                    paramList.RemoveAt(paramList.Count - 1);

                    //sql语句集合中添加元素
                    arrSqlList.Add(sqlText + "" + "UNION" + "");
                }

                //把sql语句集合中的元素组合成一条sql语句
                for (int k = 0; k < arrSqlList.Count; k++)
                {
                    sql += arrSqlList[k];
                }

                //去掉sql中最后5个字符
                sql = sql.Substring(0, sql.Length - 5);

                if (sql == null || sql.Length == 0)
                    return null;

                DbCommand dbCommand = db.GetSqlStringCommand(sql);

                DataTable dt = new DataTable();

                dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region union组合sql语句
        /// <summary>
        /// 组合sql语句
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="arrList"></param>
        /// <returns></returns>
        public static DataTable ExecuteSqlCombine(string pageId, string sqlId, ArrayList arrList)
        {
            Database db = DatabaseFactory.CreateDatabase();

            //sql语句集合
            ArrayList arrSqlList = new ArrayList();

            //初始sql语句
            string sqlText = "";

            //转换前的初始sql
            string sql = "";

            ArrayList arrParamList = new ArrayList();

            try
            {
                if (arrList.Count != 0)
                {
                    int i = 0;
                    for (i = 0; i < arrList.Count; i = i + 2)
                    {
                        arrParamList.Clear();

                        arrParamList.Add(arrList[i]);

                        arrParamList.Add(Convert.ToInt32(arrList[i + 1]));

                        sqlText = XmlProcedure.BuildCombineString(pageId, sqlId, arrParamList);

                        arrSqlList.Add(sqlText + "" + "UNION All" + "");
                    }
                    //把sql语句集合中的元素组合成一条sql语句
                    for (int k = 0; k < arrSqlList.Count; k++)
                    {
                        sql += arrSqlList[k];
                    }

                    //去掉sql中最后5个字符
                    sql = sql.Substring(0, sql.Length - 9);

                    if (sql == null || sql.Length == 0)
                        return null;

                    DbCommand dbCommand = db.GetSqlStringCommand(sql);

                    DataTable dt = new DataTable();

                    dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region union组合sql语句
        /// <summary>
        /// 组合sql语句
        /// </summary>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <param name="arrList"></param>
        /// <returns></returns>
        public static DataTable ExecuteSqlAllCombine(string pageId, string sqlId, ArrayList arrList)
        {
            Database db = DatabaseFactory.CreateDatabase();

            //sql语句集合
            ArrayList arrSqlList = new ArrayList();

            //转换前的初始sql
            string sql = "";

            ArrayList arrParamList = new ArrayList();

            try
            {
                if (arrList.Count != 0)
                {
                    for (int i = 0; i < arrList.Count; i++)
                    {
                        arrParamList.Clear();

                        for (int j = 0; j < ((ArrayList)arrList[i]).Count; j++)
                        {
                            arrParamList.Add(((ArrayList)arrList[i])[j]);
                        }

                        if (i != 0)
                        {
                            sql += " UNION ALL ";
                        }

                        sql += "SELECT * FROM (";

                        sql += XmlProcedure.BuildCombineString(pageId, sqlId, arrParamList);

                        sql = sql + ") A" + i;

                    }

                    if (sql == null || sql.Length == 0)
                        return null;

                    DbCommand dbCommand = db.GetSqlStringCommand(sql);

                    DataTable dt = new DataTable();

                    dt.Load(db.ExecuteReader(dbCommand), LoadOption.OverwriteChanges);

                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion
    }
}
